﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccessLibrary;

namespace BusinessLayer
{
    public class ClsWorkingHours
    {        
        public int WorkingHoursID { get; set; }
        public DateTime  DateFrom { get; set; }
        public DateTime DateTo { get; set; }
        public DateTime StartWorkingTime { get; set; }
        public DateTime OffWorkingTime { get; set; }
        public int MinimumWorkingHour { get; set; }
        public bool ActiveStatus { get; set; }
        public decimal NoOfHours { get; set; }
        public decimal MinimumHourForOverTime { get; set; }
        
        public DataTable GetAllWorkingHours()
        {
            using (OleDataClient data = new OleDataClient())
            {
                return data.GetDataTable("Select * from WorkingHours", "WorkingHours");
            }
        }
        public void GetWorkingHour(int intWorkingHoursID)
        {
            using (OleDataClient data = new OleDataClient())
            {
                DataTable dt = data.GetDataTable("Select * from WorkingHours where WorkingHoursID=" + intWorkingHoursID, "WorkingHours");
                if (dt.Rows.Count > 0)
                {
                    WorkingHoursID = Convert.ToInt32(dt.Rows[0]["WorkingHoursID"]);
                    DateFrom = Convert.ToDateTime(dt.Rows[0]["DateFrom"]);
                    DateTo = Convert.ToDateTime(dt.Rows[0]["DateTo"]);
                    StartWorkingTime =Convert.ToDateTime(dt.Rows[0]["StartWorkingTime"]);
                    OffWorkingTime = Convert.ToDateTime(dt.Rows[0]["OffWorkingTime"]);
                    MinimumWorkingHour = Convert.ToInt32(dt.Rows[0]["MinimumWorkingHour"]);
                    NoOfHours = Utility.GetNoOfHours(StartWorkingTime, OffWorkingTime);
                    MinimumHourForOverTime = Convert.ToInt32(dt.Rows[0]["MinimumHourForOverTime"]);
                }
            }
        }


        public bool  GetCurrentWorkingHour(DateTime FromDate, DateTime ToDate)
        {
            DataTable dt = new DataTable();
            using (OleDataClient data = new OleDataClient())
            {
                dt = data.GetDataTable("Select * from WorkingHours where Format([DateFrom],'yyyy-mm-dd') <= '" + Utility.GetDateYYYYMMDD(FromDate) + "' and Format([DateTo],'yyyy-mm-dd') >= '" + Utility.GetDateYYYYMMDD(ToDate) + "'", "WorkingHours");
                if (dt.Rows.Count > 0)
                {
                    WorkingHoursID = Convert.ToInt32(dt.Rows[0]["WorkingHoursID"]);
                    DateFrom = Convert.ToDateTime(dt.Rows[0]["DateFrom"]);
                    DateTo = Convert.ToDateTime(dt.Rows[0]["DateTo"]);
                    StartWorkingTime = Convert.ToDateTime(dt.Rows[0]["StartWorkingTime"]);
                    OffWorkingTime = Convert.ToDateTime(dt.Rows[0]["OffWorkingTime"]);
                    MinimumWorkingHour = Convert.ToInt32(dt.Rows[0]["MinimumWorkingHour"]);
                    NoOfHours = Utility.GetNoOfHours(StartWorkingTime, OffWorkingTime);
                    MinimumHourForOverTime = Convert.ToInt32(dt.Rows[0]["MinimumHourForOverTime"]);
                    return true;
                }
                else
                {
                    return false;;
                }
            }
        }

        public bool SaveWorkingHour(string KeyForSave)
        {
            using (OleDataClient data = new OleDataClient())
            {
                if (KeyForSave == "add")
                {

                    string count = data.GetValue("select count(*) from WorkingHours where  Format([DateFrom],'yyyy-mm-dd')  between '" + Utility.GetDateYYYYMMDD(DateFrom) + "'  and '" + Utility.GetDateYYYYMMDD(DateFrom) + "' or Format([DateTo],'yyyy-mm-dd')  between '" + Utility.GetDateYYYYMMDD(DateTo) + "'  and '" + Utility.GetDateYYYYMMDD(DateTo) + "'");
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Working Hours already exist");
                    }
                    else
                    {
                        data.ExecuteNonQuery("Insert into WorkingHours (DateFrom,DateTo,StartWorkingTime,OffWorkingTime,MinimumWorkingHour) values('" + DateFrom + "','" + DateTo + "','" + StartWorkingTime + "','" + OffWorkingTime + "'," + MinimumWorkingHour + " )");
                    }
                    
                }
                else
                {

                    string count = data.GetValue("select count(*) from WorkingHours where  Format([DateFrom],'yyyy-mm-dd')  between '" + Utility.GetDateYYYYMMDD(DateFrom) + "'  and '" + Utility.GetDateYYYYMMDD(DateFrom) + "' or Format([DateTo],'yyyy-mm-dd')  between '" + Utility.GetDateYYYYMMDD(DateTo) + "'  and '" + Utility.GetDateYYYYMMDD(DateTo) + "'");
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Working Hours already exist");
                    }
                    else
                    {
                        data.ExecuteNonQuery("Update WorkingHours set DateFrom = '" + DateFrom + "', DateTo =  '" + DateTo + "', StartWorkingTime =  '" + StartWorkingTime + "', OffWorkingTime =  '" + OffWorkingTime + "', MinimumWorkingHour =  " + MinimumWorkingHour + " where WorkingHoursID=" + WorkingHoursID.ToString());
                    }
                    
                    
                }
            }
            return true;
        }
        public void DeleteWorkingHour()
        {
            using (OleDataClient data = new OleDataClient())
            {
                data.ExecuteNonQuery("Delete from  WorkingHours where WorkingHoursID=" + WorkingHoursID.ToString());
            }
        }
    }
}
